import pandas as pd
import numpy as np

'''
wD = pd.read_excel('测风数据2.0（完整）杨明海&焦志雄 .xlsx',parse_dates=['Date & Time Stamp'])
date=[]
#print(wD)

wD['hour']=wD['Date & Time Stamp'].dt.hour
wD['day']=wD['Date & Time Stamp'].dt.day


wD['month_day_hour']=wD['Date & Time Stamp'].apply(lambda x : x.strftime('%m-%d %H'))

wD.to_excel('测风数据3.0.xlsx')
'''

wD = pd.read_excel('测风数据3.0.xlsx',parse_dates=['Date & Time Stamp'])

date=[]
for d in wD['month_day_hour']:
    date.append(d)
    #print(d)
    
zero=[0]*len(date)
#zlist=[[]]*len(date)


date = np.unique(date)
vHourAVG=dict(zip(date,zero))
vHourSD6=dict.fromkeys(date,None)
vHourSD=dict(zip(date,zero))
# vHourSD6[date[1]].append(21)
# print(vHourSD6)
# print(vHourSD6[date[2]])
for d in vHourSD6:
    vHourSD6[d]=[]

n=0
for d in wD['month_day_hour']:
    vHourAVG[d]+=wD['CH1Avg'][n:n+1].values
    vHourSD6[d].append(wD['CH1Avg'][n:n+1].values)
    #print(vHourSD6[d],':',type(vHourSD6[d]))
    n+=1

#每小时平均风速
print(date)
for key in vHourAVG:
    vHourAVG[key]=format(float(vHourAVG[key]/6),'.2f')
    #print(vHourAVG[key])
vHourAVGDirc = pd.DataFrame.from_dict(vHourAVG,orient='index',columns=['70m'])
vHourAVGDirc = vHourAVGDirc.reset_index().rename(columns={'index':'month_day_hour'})
#vHourAVGDirc = pd.DataFrame(vHourAVG,index=[0])

#vHourAVGDirc.to_excel('每小时平均风速.xlsx')

#每小时平均方差
for d in vHourAVG:
    vHourSD[d]=format(np.std(vHourSD6[d]),'.3f')

vHourSDDirc = pd.DataFrame.from_dict(vHourSD,orient='index',columns=['70mSD'])
vHourSDDirc = vHourSDDirc.reset_index().rename(columns={'index':'month_day_hour'})
# print(vHourAVGDirc)
# print(vHourSDDirc)
result_70=pd.merge(vHourAVGDirc,vHourSDDirc,on='month_day_hour')

# for i in vHourSD:
#     print(vHourSD[i])
result_70.to_excel('70m每小时风速和每小时风速方差.xlsx')
print(result_70)


